﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using VatTuDTO;
using System.Data;

namespace VatTuDAO
{
    public class SanPhamDAO: BaseDataProvider 
    {
        MySqlCommand _cmd;
        // Lấy 1 sản phẩm có mã
        public SanPhamDTO SelectSanPham(int maSanPham)
        {
            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "select * from SanPham where MaSanPham=@MaSanPham";
            _cmd.CommandType = CommandType.Text;
            _cmd.Parameters.Add(new MySqlParameter("@MaSanPham",maSanPham));
            DataTable dt = new DataTable();
            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count <= 0)
            {
                return null;
            }

            SanPhamDTO dto = new SanPhamDTO();

            try
            {

                DataRow row = dt.Rows[0];//kết quả trả ra ở dòng 0


                dto.MaSanPham = int.Parse(row["MaSanPham"].ToString());
                dto.TenSanPham = row["TenSanPham"].ToString();
                dto.TenNhaSanXuat= row["TenNhaSanXuat"].ToString();
                dto.DonViTinh = row["DonViTinh"].ToString();
                dto.GiaBan =int.Parse( row["GiaBan"].ToString());
                dto.MoTa= row["MoTa"].ToString();
                dto.SoLuongTon =int.Parse(row["SoLuongTon"].ToString());
                dto.HinhAnh = row["HinhAnh"].ToString();
                dto.MaLoaiSanPham =int.Parse( row["MaLoaiSanPham"].ToString());
                dto.AnHien = int.Parse(row["AnHien"].ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dto;
        }
        //Lấy danh sách sản phẩm
        public List<SanPhamDTO> SelectSanPham()
        {
            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "select * from SanPham";
            _cmd.CommandType = CommandType.Text;
           
            DataTable dt = new DataTable();
            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            List<SanPhamDTO> ds = new List<SanPhamDTO>();
            SanPhamDTO dto;

            try
            {

                foreach (DataRow row in dt.Rows)
                {
                    dto = new SanPhamDTO();
                    dto.MaSanPham = int.Parse(row["MaSanPham"].ToString());
                    dto.TenSanPham = row["TenSanPham"].ToString();
                    dto.TenNhaSanXuat = row["TenNhaSanXuat"].ToString();
                    dto.DonViTinh = row["DonViTinh"].ToString();
                    dto.GiaBan = int.Parse(row["GiaBan"].ToString());
                    dto.MoTa = row["MoTa"].ToString();
                    dto.SoLuongTon = int.Parse(row["SoLuongTon"].ToString());
                    dto.HinhAnh = row["HinhAnh"].ToString();
                    dto.MaLoaiSanPham = int.Parse(row["MaLoaiSanPham"].ToString());
                    dto.AnHien = int.Parse(row["AnHien"].ToString());

                    ds.Add(dto);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return ds;
        }
        // Lấy sanh sách sản phẩm theo Loai
        public List<SanPhamDTO> SelectSanPhamTheoLoai(int maLoaiSanPham)
        {
            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "select * from SanPham where MaLoaiSanPham=@MaLoaiSanPham";
            _cmd.CommandType = CommandType.Text;
            _cmd.Parameters.Add("@MaLoaiSanPham",maLoaiSanPham);

            DataTable dt = new DataTable();
            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            List<SanPhamDTO> ds = new List<SanPhamDTO>();
            SanPhamDTO dto;

            try
            {

                foreach (DataRow row in dt.Rows)
                {
                    dto = new SanPhamDTO();
                    dto.MaSanPham = int.Parse(row["MaSanPham"].ToString());
                    dto.TenSanPham = row["TenSanPham"].ToString();
                    dto.TenNhaSanXuat = row["TenNhaSanXuat"].ToString();
                    dto.DonViTinh = row["DonViTinh"].ToString();
                    dto.GiaBan = int.Parse(row["GiaBan"].ToString());
                    dto.MoTa = row["MoTa"].ToString();
                    dto.SoLuongTon = int.Parse(row["SoLuongTon"].ToString());
                    dto.HinhAnh = row["HinhAnh"].ToString();
                    dto.MaLoaiSanPham = int.Parse(row["MaLoaiSanPham"].ToString());
                    dto.AnHien = int.Parse(row["AnHien"].ToString());

                    ds.Add(dto);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return ds;
        }

        // Lấy sanh sách san pham theo chung loai
       
        public List<SanPhamDTO> SelectSanPhamTheoChungLoai(int maChungLoai)
        {
            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "select MaSanPham,TenSanPham,TenNhaSanXuat,DonViTinh,GiaBan,MoTa,"+
                                "SoLuongTon,HinhAnh,sp.MaLoaiSanPham,AnHien from SanPham sp,LoaiSanPham lsp,ChungLoai cl "+
                                "where sp.MaLoaiSanPham=lsp.MaLoaiSanPham and lsp.MaChungLoai=cl.MaChungLoai"; //and cl.MaChungLoai=@MaChungLoai";
            _cmd.CommandType = CommandType.Text;

         //   _cmd.Parameters.Add(new MySqlParameter("@MaChungLoai", maChungLoai));

            DataTable dt = new DataTable();
            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            List<SanPhamDTO> ds = new List<SanPhamDTO>();
            SanPhamDTO dto;

            try
            {

                foreach (DataRow row in dt.Rows)
                {
                    dto = new SanPhamDTO();
                    dto.MaSanPham = int.Parse(row["MaSanPham"].ToString());
                    dto.TenSanPham = row["TenSanPham"].ToString();
                    dto.TenNhaSanXuat = row["TenNhaSanXuat"].ToString();
                    dto.DonViTinh = row["DonViTinh"].ToString();
                    dto.GiaBan = int.Parse(row["GiaBan"].ToString());
                    dto.MoTa = row["MoTa"].ToString();
                    dto.SoLuongTon = int.Parse(row["SoLuongTon"].ToString());
                    dto.HinhAnh = row["HinhAnh"].ToString();
                    dto.MaLoaiSanPham = int.Parse(row["MaLoaiSanPham"].ToString());
                    dto.AnHien = int.Parse(row["AnHien"].ToString());

                    ds.Add(dto);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return ds;
        }
        //-------------------------------------------
        public Boolean ThemSanPham(SanPhamDTO dto)
        {

            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "insert into SanPham(TenSanPham,TenNhaSanXuat,DonViTinh,GiaBan,MoTa,SoLuongTon,HinhAnh,MaLoaiSanPham,AnHien)";
            _cmd.CommandText += " values(@TenSanPham,@TenNhaSanXuat,@DonViTinh,@GiaBan,@MoTa,@SoLuongTon,@HinhAnh,@MaLoaiSanPham,@AnHien)";
            _cmd.CommandType = CommandType.Text;
            _cmd.Parameters.Add(new MySqlParameter("@TenSanPham", dto.TenSanPham));
            _cmd.Parameters.Add(new MySqlParameter("@TenNhaSanXuat", dto.TenNhaSanXuat));
            _cmd.Parameters.Add(new MySqlParameter("@DonViTinh", dto.DonViTinh));
            _cmd.Parameters.Add(new MySqlParameter("@GiaBan", dto.GiaBan));
            _cmd.Parameters.Add(new MySqlParameter("@MoTa", dto.MoTa));
            _cmd.Parameters.Add(new MySqlParameter("@SoLuongTon", dto.SoLuongTon));
            _cmd.Parameters.Add(new MySqlParameter("@HinhAnh", dto.HinhAnh));
            _cmd.Parameters.Add(new MySqlParameter("@MaLoaiSanPham", dto.MaLoaiSanPham));
            _cmd.Parameters.Add(new MySqlParameter("@AnHien", dto.AnHien));
            if (ExecuteNonQuery(_cmd) == false)
            {
                return false;
            }

            return true;
        }

        //--------------------------------
        public Boolean CapNhatSanPham(SanPhamDTO dto)
        {

            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "update SanPham set TenSanPham=@TenSanPham,TenNhaSanXuat=@TenNhaSanXuat,";
            _cmd.CommandText += "DonViTinh=@DonViTinh,GiaBan=@GiaBan,MoTa=@MoTa,SoLuongTon=@SoLuongTon,HinhAnh=@HinhAnh,MaLoaiSanPham=@MaLoaiSanPham,AnHien=@AnHien";
            _cmd.CommandText += " where MaSanPham=@MaSanPham";
            _cmd.CommandType = CommandType.Text;
            _cmd.Parameters.Add(new MySqlParameter("@TenSanPham", dto.TenSanPham));
            _cmd.Parameters.Add(new MySqlParameter("@TenNhaSanXuat", dto.TenNhaSanXuat));
            _cmd.Parameters.Add(new MySqlParameter("@DonViTinh", dto.DonViTinh));
            _cmd.Parameters.Add(new MySqlParameter("@GiaBan", dto.GiaBan));
            _cmd.Parameters.Add(new MySqlParameter("@MoTa", dto.MoTa));
            _cmd.Parameters.Add(new MySqlParameter("@SoLuongTon", dto.SoLuongTon));
            _cmd.Parameters.Add(new MySqlParameter("@HinhAnh", dto.HinhAnh));
            _cmd.Parameters.Add(new MySqlParameter("@MaLoaiSanPham", dto.MaLoaiSanPham));
            _cmd.Parameters.Add(new MySqlParameter("@AnHien", dto.AnHien));
            _cmd.Parameters.Add(new MySqlParameter("@MaSanPham", dto.MaSanPham));

            if (ExecuteNonQuery(_cmd) == false)
            {
                return false;
            }

            return true;
        }
        //----------------------------------------
        public Boolean XoaSanPham(int MaSanPham)
        {

            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "delete from SanPham";
            _cmd.CommandText += " where MaSanPham=@MaSanPham";
            _cmd.CommandType = CommandType.Text;
            _cmd.Parameters.Add(new MySqlParameter("@MaSanPham", MaSanPham));

            if (ExecuteNonQuery(_cmd) == false)
            {
                return false;
            }

            return true;
        }

        //--------------------------------------
        public List<SanPhamDTO> SelectSanPham(String key_search)
        {
           
            String condition = "TenSanPham like N'%" + key_search + "%' or ";
            condition = condition + "1=0";


            List<SanPhamDTO> LspDTO = new List<SanPhamDTO>();

            _cmd = CreateMysqlCommand();
            _cmd.CommandText = "select * from SanPham where " + condition;
            _cmd.CommandType = CommandType.Text;


            DataTable dt = new DataTable();
            dt = ExecuteSelectCommand(_cmd);

            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            List<SanPhamDTO> ds = new List<SanPhamDTO>();
            SanPhamDTO dto;

            try
            {

                foreach (DataRow row in dt.Rows)
                {
                    dto = new SanPhamDTO();
                    dto.MaSanPham = int.Parse(row["MaSanPham"].ToString());
                    dto.TenSanPham = row["TenSanPham"].ToString();
                    dto.TenNhaSanXuat = row["TenNhaSanXuat"].ToString();
                    dto.DonViTinh = row["DonViTinh"].ToString();
                    dto.GiaBan = int.Parse(row["GiaBan"].ToString());
                    dto.MoTa = row["MoTa"].ToString();
                    dto.SoLuongTon = int.Parse(row["SoLuongTon"].ToString());
                    dto.HinhAnh = row["HinhAnh"].ToString();
                    dto.MaLoaiSanPham = int.Parse(row["MaLoaiSanPham"].ToString());
                    dto.AnHien = int.Parse(row["AnHien"].ToString());

                    ds.Add(dto);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return ds;
        }
    }
}
